<?php
/**
 * Desc: information_shcema数据库
 * User: zdb
 * Date: 2024/8/12
 * Time: 15:41
 */
namespace app\api\service;
use app\api\service\develop\TableFieldService;
use app\api\service\develop\TableService;
use app\api\service\system\SystemFieldService;
use think\facade\Db;
use think\facade\Env;

class SchemaService
{
    private $dbConn;

    public function __construct()
    {
        if (!$this->dbConn) {
            $this->dbConn = Db::connect('mysql_schema');
        }
    }

    public function getDbConnection()
    {
        return $this->dbConn;
    }

    /**
     * Desc: 自动填充数据库表字段信息
     * 删除表：DROP TABLE `yj_system_field`;
     * Datetime: 2024/8/12 15:45 by zdb
     */
    public function autoFillTableField()
    {
        $dbName = "memory";
        $allTableList = $this->getTableList($dbName);
        $columnTableName = array_column($allTableList, 'table_name');
        $tableNameStr = "('".implode("','", $columnTableName)."')";
        $sql = "SELECT c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.COLUMN_COMMENT FROM information_schema.`COLUMNS` c 
            WHERE c.TABLE_SCHEMA = '".$dbName."' AND c.TABLE_NAME IN $tableNameStr";
        $listField = $this->getDbConnection()->query($sql);
        $serviceSystemField = new TableFieldService();

        $commentByTableName = array_column($allTableList, 'comment', 'table_name');

        $serviceTable = new TableService();
        $list = $serviceTable->getListNoPage();
        $flagInsertTable = array_column($list, 'id', 'table_name');
        $modelTable = Db::table('yj_table');
        $modelTable->startTrans();
        $flag = true;
        try{
            foreach($listField as $item){
                $tableName = $item['TABLE_NAME'];
                echo "table Name:".$tableName."<br>";
                var_dump($flagInsertTable[$tableName]);
                if(!isset($flagInsertTable[$tableName])){
                    $tableComment = $commentByTableName[$tableName] ?? '';
                    $tableId = $serviceTable->createRow(['table_name' => $tableName, 'comment' => $tableComment]);
                    if($tableId){
                        $flagInsertTable[$tableName] = $tableId;
                    }else{
                        $flag = false;
                        continue;
                    }
                }else{
                    $tableId = $flagInsertTable[$tableName];
                }
                $data = [
                    'table_id'   => $tableId,
                    'table_name' => $item['TABLE_NAME'],
                    'field_name' => $item['COLUMN_NAME'],
                    'field_comment' => $item['COLUMN_COMMENT'],
                    'data_type' => $item['DATA_TYPE'],
                ];
                $serviceSystemField->createRow($data);
            }
            if($flag){
                $modelTable->commit();
            }else{
                $modelTable->rollback();
                echo "fail";
            }
        }catch(\Exception $e){
            $modelTable->rollback();
            echo $e->getMessage();
        }

    }

    public function getTableList($dbName)
    {
        $sql = "SELECT t.TABLE_NAME AS table_name, t.TABLE_COMMENT AS comment FROM `TABLES` t WHERE t.TABLE_SCHEMA = '".$dbName."'";
        return $this->getDbConnection()->query($sql);
    }

    public function getOriginalTableFieldList($tableName)
    {
        $dbName = Env::get("DATABASE_PRIMARY.DB_NAME");
        $sql = "SELECT c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.COLUMN_COMMENT FROM information_schema.`COLUMNS` c 
            WHERE c.TABLE_SCHEMA = '".$dbName."' AND c.TABLE_NAME = '$tableName'";
        $listField = $this->getDbConnection()->query($sql);
        return $listField;
    }

}